﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Linq.Expressions;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Moq;
using MyBasicLib.Data;
using MyBasicLib.Data.ORMLiteNew;
using MyBasicLib.Data.ORMLiteNew.SqlClient;
using MyBasicLib.Utilities;
using RUC.Core.Entities;

namespace MyBasicLib.Test.Data.ORMLiteNew.SqlClient
{
    [TestClass]
    public class UnitTest1
    {
        [ClassInitialize()]
        public static void MyClassInitialize(TestContext testContext)
        {
            IOC.DefaultContainer.Register<ILogger>(() => new Mock<ILogger>().Object, true);

            IOC.DefaultContainer.Register<IDatabase>(() => new Mock<IDatabase>().Object, true);
            var executor = IOC.DefaultContainer.Resolve<IDatabase>();
            var mock = Moq.Mock.Get(executor);
            mock.Setup(p => p.CreateCommand(It.IsAny<string>())).Returns<string>(p => new System.Data.SqlClient.SqlCommand((string)p)); ;
            mock.Setup(p => p.Execute<int>(null, It.IsAny<System.Data.SqlClient.SqlCommand>())).Returns(100);
            mock.Setup(p => p.Execute<int>(null, It.IsAny<System.Data.SqlClient.SqlCommand>())).Returns(100);
       
            Dc = new DataContext();

        }
        static DataContext Dc;

        void AssertHelper(string expectedSql)
        {
            var executor = IOC.DefaultContainer.Resolve<IDatabase>();
            var mock = Moq.Mock.Get(executor);
            mock.Verify(p => p.CreateCommand(expectedSql));
        }
        [TestMethod]
        public void TestMethodBracket11()
        {
            var q = from a in Dc.GetTable<App>()
                    where a.Id == "" | a.Id == ""
                    select a.Id;
            q.ToArray();
            var expectedSql = "SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE [t0].[Id] = @p0 OR [t0].[Id] = @p1";
            AssertHelper(expectedSql);
        }
        [TestMethod]
        public void TestMethodBracket2()
        {
            var df = typeof(Moq.Mock<MyBasicLib.ILogger>);
            var q = from a in Dc.GetTable<App>()
                    where a.Id == "" || a.Id == "" || a.Id == ""
                    select a.Id;
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE [t0].[Id] = @p0 OR [t0].[Id] = @p1 OR [t0].[Id] = @p2");
        }
        [TestMethod]
        public void TestMethodBracket3()
        {

            var q = from a in Dc.GetTable<App>()
                    where a.Id == "" && a.Id == "" || a.Id == ""
                    select a.Id;
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE [t0].[Id] = @p0 AND [t0].[Id] = @p1 OR [t0].[Id] = @p2");
        }
        [TestMethod]
        public void TestMethodBracket31()
        {

            var q = from a in Dc.GetTable<App>()
                    where a.Id == "" & a.Id == "" | a.Id == ""
                    select a.Id;
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE [t0].[Id] = @p0 AND [t0].[Id] = @p1 OR [t0].[Id] = @p2");
        }
        [TestMethod]
        public void TestMethodBracket4()
        {

            var q = from a in Dc.GetTable<App>()
                    where a.Id == "" || a.Id == "" && a.Id == ""
                    select a.Id;
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE [t0].[Id] = @p0 OR [t0].[Id] = @p1 AND [t0].[Id] = @p2");
        }
        [TestMethod]
        public void TestMethodBracket5()
        {

            var q = from a in Dc.GetTable<App>()
                    where (a.Id == "" || a.Id == "") && a.Id == ""
                    select a.Id;
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE ([t0].[Id] = @p0 OR [t0].[Id] = @p1) AND [t0].[Id] = @p2");
        }

        [TestMethod]
        public void TestMethodBracket6()
        {
            var q = from a in Dc.GetTable<App>()
                    where (a.Id == "" && a.Id == "" || a.Id == "") && a.Id == ""
                    select a.Id;
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE ([t0].[Id] = @p0 AND [t0].[Id] = @p1 OR [t0].[Id] = @p2) AND [t0].[Id] = @p3");
        }
        [TestMethod]
        public void TestMethodBracket7()
        {
            var q = from a in Dc.GetTable<App>()
                    where !(a.Id == "" || a.Id == "")
                    select a.Id;
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE NOT ([t0].[Id] = @p0 OR [t0].[Id] = @p1)");
            // SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE  NOT ([t0].[Id] = @p0 OR [t0].[Id] = @p1)
        }
        [TestMethod]
        public void TestMethodBracket8()
        {
            var q = from a in Dc.GetTable<App>()
                    where !(a.Id == "") || a.Id == ""
                    select a.Id;
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE NOT [t0].[Id] = @p0 OR [t0].[Id] = @p1");
            // SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE  NOT ([t0].[Id] = @p0 OR [t0].[Id] = @p1)
        }

        [TestMethod]
        public void TestMethodBracket9()
        {
            var q = from a in Dc.GetTable<App>()
                    where (~SqlMethods.Len(a.Id) ^ 1) > 0 && !(a.Id == "")
                    // where  a.Name == "" || a.Id == "" && a.Created > DateTime.Now
                    select a.Id;
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE ~ LEN([t0].[Id]) ^ @p0 > @p1 AND NOT [t0].[Id] = @p2");

        }

        [TestMethod]
        public void TestMethodBracket11xx()
        {
            var q = from a in Dc.GetTable<App>()
                    where !(a.Id == "") || a.Id == ""
                    select a.Id;
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE NOT [t0].[Id] = @p0 OR [t0].[Id] = @p1");
        }

        [TestMethod]
        public void TestMethodAdd()
        {
            var q = from a in Dc.GetTable<App>()
                    where ((DateTime)a.Created).AddYears((int)SqlMethods.Len(a.Id)) > DateTime.Now
                    select a.Id;
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE DATEADD(YEAR,LEN([t0].[Id]),[t0].[Created]) > @p0");
        }
        [TestMethod]
        public void TestMethodAdd1()
        {
            var q = from a in Dc.GetTable<App>()
                    where a.Created.AddYears(1) > DateTime.Now
                    select a.Id;
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE DATEADD(YEAR,@p0,[t0].[Created]) > @p1");
        }
        //[TestMethod]
        //public void TestMethodUNION1()
        //{
        //    var q = (from a in Dc.GetTable<App>()
        //             where a.Id == ""
        //             select a.Id).Union(
        //             from a in Dc.GetTable<App>()
        //             where a.Name == ""
        //             select a.Id
        //            );
        //    q.ToArray();
        //    AssertHelper("SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE ~ LEN([t0].[Id]) ^ @p0 > @p1 AND NOT [t0].[Id] = @p2");

        //}


        #region ExceptionTest
        [TestMethod]
        public void ExceptionTestMethod()
        {
            var q = Dc.GetTable<App>().Where(a => a.Id == "").Select(p => p).ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t1].[Id] = @p0");
        }

        [TestMethod]
        [ExpectedException(typeof(NotSupportedException), "请在select语句中使用类似p=>p而不是p=>new App()的语法")]
        public void ExceptionTestMethod1()
        {
            var q = Dc.GetTable<RUC.Core.Entities.App>().Select(p => new App()).ToArray();
        }

        [TestMethod]
        [ExpectedException(typeof(NotSupportedException), "对于中间查询结果（需要进一步过滤的查询），请写成(a, u) => new { a, u }，而不是(a,u)=>new{a.Id...}")]
        public void ExceptionTestMethod2()
        {
            var q = Dc.GetTable<RUC.Core.Entities.App>().Select(p => new { p.Id }).Select(p => p.Id).ToArray();
        }
        [TestMethod]
        [ExpectedException(typeof(NotSupportedException), "对于中间查询结果（需要进一步过滤的查询），请写成(a, u) => new { a, u }，而不是(a,u)=>new{a.Id...}")]
        public void ExceptionTestMethod3()
        {
            var q = Dc.GetTable<RUC.Core.Entities.App>().Select(p => new { p.Id }).Where(p => p.Id == string.Empty).ToArray();
        }

        #endregion
        [TestMethod]
        public void FullTextContainsTest()
        {
            var q = Dc.GetTable<App>().Where(p => SqlMethods.FullTextContains(p.Id, "x")).Select(p => p.Id).ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id] FROM [App] [t0] WHERE CONTAINS ([t0].[Id],@p0)");
        }
        [TestMethod]
        public void TestMethodLength()
        {

            var q = (from a in Dc.GetTable<App>()
                     where SqlMethods.Len(a.Name) > 0
                     select a).ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE LEN([t0].[Name]) > @p0");
        }

        [TestMethod]
        public void TestMethodSelect()
        {

            var q = (from a in Dc.GetTable<App>()
                     select new App() { Id = a.Name }).ToArray();
            AssertHelper("SELECT [t0].[Name] AS [Id] FROM [App] [t0]");
        }
        [TestMethod]

        public void TestMethodBitAnd()
        {
            var q = (from a in Dc.GetTable<App>()
                     where (SqlMethods.Len(a.Name) & 0) > 0
                     select a).ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE LEN([t0].[Name]) & @p0 > @p1");
        }
        [TestMethod]

        public void TestMethodBitOr()
        {
            var q = (from a in Dc.GetTable<App>()
                     where (SqlMethods.Len(a.Name) | 0) > 0
                     select a).ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE LEN([t0].[Name]) | @p0 > @p1");
        }
        //[TestMethod]

        //public void TestMethodBitV()
        //{
        //    var q = (from a in Dc.GetTable<App>()
        //             where (SqlMethods.Len(a.Name) ^ 0) > 0
        //             select a).ToArray();
        //    AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE (LEN([t0].[Name]) | @p0) > @p1");
        //}

        //---------------------------------------
        [TestMethod]
        public void TestMethodLeftJoin1()
        {


            var q = from a in Dc.GetTable<App>()
                    join u in Dc.GetTable<AppUser>()
                    on a.Id equals u.AppId into au
                    from u in au.DefaultIfEmpty()
                    //group a by a.Name into g
                    //where u.Name ==""；
                    //orderby a.Name
                    select new { a.Name, AppName = u.Name };
            q.ToArray();
            AssertHelper("SELECT [t0].[Name] AS [Name],[t1].[Name] AS [AppName] FROM [App] [t0] LEFT JOIN [AppUser] [t1] ON [t0].[Id]=[t1].[AppId]");

            // AssertHelper(q, "SELECT [t0].[Name] AS [Name],[t1].[Name] AS [AppName] FROM [App] [t0] LEFT JOIN [AppUser] [t1] ON [t0].[Id]=[t1].[AppId]");
        }

        [TestMethod]
        public void TestMethodLeftJoin2()
        {
            var dc = Dc;
            var q = from a in dc.GetTable<App>()
                    join au in dc.GetTable<AppUser>()
                    on a.Id equals au.AppId
                    join um in dc.GetTable<UserMap>()
                        on au.Id equals um.AppUserId
                        into map1
                    from um in map1.DefaultIfEmpty()
                    join u in dc.GetTable<User>()
                    on um.UserId equals u.Id
                    into map2
                    from u in map2.DefaultIfEmpty()
                    where um.UserId == ""
                    select new
                    {
                        //  EquipmentId = i.EquipmentID,
                        a.Id,
                        AppUserNAme = au.Name,
                        u.Name
                    };
            //select new {g.Key,Count=g.Count()}
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t1].[Name] AS [AppUserNAme],[t3].[Name] AS [Name] FROM [App] [t0] INNER JOIN [AppUser] [t1] ON [t0].[Id]=[t1].[AppId]  LEFT JOIN [AppUserUserMap] [t2] ON [t1].[Id]=[t2].[AppUserId]  LEFT JOIN [User] [t3] ON [t2].[UserId]=[t3].[Id] WHERE [t2].[UserId] = @p0");
        }
        [TestMethod]
        public void TestMethodSelectMany1()
        {
            var q = from a in Dc.GetTable<App>()
                    from u in Dc.GetTable<AppUser>()
                    where a.Id == u.AppId
                    select new { a.Name, AppName = u.Name };
            //select new {g.Key,Count=g.Count()}
            q.ToArray();
            AssertHelper("SELECT [t0].[Name] AS [Name],[t1].[Name] AS [AppName] FROM [App] [t0] CROSS JOIN [AppUser] [t1]  WHERE [t0].[Id] = [t1].[AppId]");
        }
        [TestMethod]
        public void TestMethodGroup1()
        {
            var q = from a in Dc.GetTable<App>()
                    join u in Dc.GetTable<AppUser>()
                    on a.Id equals u.AppId into au
                    from u in au.DefaultIfEmpty()
                    //group a by a.Name into g
                    //where u.Name ==""
                    select new { a.Name, AppName = u.Name };
            //select new {g.Key,Count=g.Count()}
            q.ToArray();

            AssertHelper("SELECT [t0].[Name] AS [Name],[t1].[Name] AS [AppName] FROM [App] [t0] LEFT JOIN [AppUser] [t1] ON [t0].[Id]=[t1].[AppId]");
        }
        [TestMethod]
        public void TestMethod1()
        {
            var q = Dc.GetTable<App>().Select(p => p);
            q.ToArray();

            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0]");
        }
        [TestMethod]
        public void TestMethod10()
        {
            var q = Dc.GetTable<App>().Select(p => p.Id);
            q.ToArray();

            AssertHelper("SELECT [t0].[Id] AS [Id] FROM [App] [t0]");
        }
        [TestMethod]
        public void TestMethod11()
        {
            var q = Dc.GetTable<App>().Select(p => new { p.Id, p.Name, Created1 = p.Created });
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created1] FROM [App] [t0]");
        }
        [TestMethod]
        public void TestMethod12()
        {
            var q = Dc.GetTable<App>().Select(p => new { p.Id, Name1 = p.Id + p.Name, p.Created });
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Id] + [t0].[Name] AS [Name1],[t0].[Created] AS [Created] FROM [App] [t0]");
        }
        [TestMethod]
        public void TestMethod13()
        {
            var q = Dc.GetTable<App>().Select(p => new App { Id = p.Id, Name = p.Name, Created = p.Created });
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0]");
        }
        [TestMethod]
        public void TestMethod14()
        {
            var q = Dc.GetTable<App>().Select(p => new App { Id = p.Id, Name = p.Id + p.Name, Created = p.Created });
            q.CreateCommand();
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Id] + [t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0]");
        }
        [TestMethod]
        public void TestMethod15()
        {

            var q = Dc.GetTable<App>().Select(p => new App { Id = p.Id, Name = DateTime.Today.AddDays(1).ToString(), Created = DateTime.Now });
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],@p0 AS [Name],@p1 AS [Created] FROM [App] [t0]");
        }
        string TestMethod16Helper(string a, DateTime b) { return a + b.ToShortDateString(); }
        [TestMethod]
        public void TestMethod16()
        {
            //Dc.Delete<App>(p=>p);
            var q = Dc.GetTable<App>().Select(p => new { Id = p.Id, Name = TestMethod16Helper("p.Name", DateTime.Now) });
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],@p0 AS [Name],@p1 AS [Created] FROM [App] [t0]");
        }
        [TestMethod]
        public void TestMethod17()
        {
            var q = from a in Dc.GetTable<App>()
                    join u in Dc.GetTable<AppUser>()
                    on a.Id equals u.AppId into au
                    from u in au.DefaultIfEmpty()
                    select new { a.Name, u.Id };
            q.ToArray();
            AssertHelper("SELECT [t0].[Name] AS [Name],[t1].[Id] AS [Id] FROM [App] [t0] LEFT JOIN [AppUser] [t1] ON [t0].[Id]=[t1].[AppId]");
        }


        [TestMethod]
        public void TestMethod111()
        {
            var q = Dc.GetTable<App>().Distinct().Select(p => p);
            q.ToArray();
            AssertHelper("SELECT DISTINCT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0]");
        }

        //------------------------------------
        [TestMethod]
        public void TestMethod2()
        {
            var q = Dc.GetTable<App>().Where(p => p.Name == "abc").Select(p => p);
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Name] = @p0");
        }
        [TestMethod]
        public void TestMethod20()
        {
            Expression<Func<App, bool>> fun = p => p.Name == "abc";
            var q = Dc.GetTable<App>().Where(fun).Select(p => p);
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Name] = @p0");
        }

        void TestMethod21_Helper<T>(T app) where T : ITable1
        {
            var q = Dc.GetTable<T>().Where(p => p.Id == app.Id).Select(p => p);
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Id] = @p0");

        }
        [TestMethod]
        public void TestMethod21()
        {
            App app = new App() { Id = NativeMethods.UuidCreateSequential().ToString() };

            TestMethod21_Helper(app);
        }

        [TestMethod]
        public void TestMethod22()
        {
            var q = Dc.GetTable<App>().Where(p => p.Name.CompareTo("") > 0).Select(p => p);
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Name] > @p0");
        }
        [TestMethod]
        public void TestMethod23()
        {
            var q = Dc.GetTable<App>().Where(p => p.Name.Contains("")).Select(p => p);
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Name] LIKE @p0");
        }
        [TestMethod]
        public void TestMethod24()
        {
            var q = Dc.GetTable<App>().Where(p => p.Name.StartsWith("")).Select(p => p);
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Name] LIKE @p0");
        }
        [TestMethod]
        public void TestMethod25()
        {
            var q = Dc.GetTable<App>().Where(p => p.Name.ToUpper() == "").Select(p => p);
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE UPPER([t0].[Name]) = @p0");
        }
        [TestMethod]
        public void TestMethod26()
        {
            var q = Dc.GetTable<App>().Where(p => SqlMethods.IsNull(p.Name)).Select(p => p);
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Name] IS NULL");
        }
        [TestMethod]
        public void TestMethod261()
        {
            var q = Dc.GetTable<App>().First(p => SqlMethods.IsNull(p.Name));

            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Name] IS NULL");
        }
        [TestMethod]
        public void TestMethod27()
        {
            var list = new List<string>() { "", "g" };
            var q = Dc.GetTable<App>().Where(p => list.Contains(p.Name)).Select(p => p);
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Name] IN (@p0,@p1)");
        }
        [TestMethod]
        public void TestMethod28()
        {
            var list = new[] { "" };
            var q = Dc.GetTable<App>().Where(p => list.Contains(p.Name)).Select(p => p);
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Name] IN (@p0)");
        }
        [TestMethod]
        public void TestMethod29()
        {
            var list = new[] { "" };
            var q = Dc.GetTable<App>().Where(p => list.Contains(p.Name)).Select(p => new { p.Id, Name = "" });
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],@p0 AS [Name] FROM [App] [t0] WHERE [t0].[Name] IN (@p1)");
        }
        //------------------------------------
        [TestMethod]
        public void TestMethod3()
        {
            var q = Dc.GetTable<App>().OrderBy(p => p.Created).Select(p => p);
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] ORDER BY [t0].[Created]");
        }

        [TestMethod]
        public void TestMethod31()
        {
            var q = Dc.GetTable<App>().OrderByDescending(p => p.Created).Select(p => p);
            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] ORDER BY [t0].[Created] DESC");
        }

        [TestMethod]
        public void TestMethod4()
        {
            var q = Dc.GetTable<App>().OrderBy(p => p.Created).Count();
            AssertHelper("SELECT COUNT(*) FROM [App] [t0]");

            //AssertHelper(q, "SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] ORDER BY [t0].[Created] DESC", 0);
        }
        [TestMethod]
        public void TestMethod5()
        {
            var q = Dc.GetTable<App>().Where(p => p.Name == "").OrderBy(p => p.Created).Select(p => p);
            var l = q.ToPagedList(1, 1);
            AssertHelper("SELECT COUNT(*) FROM [App] [t0] WHERE [t0].[Name] = @p0");
            AssertHelper("SELECT TOP (@p0) [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Name] = @p1 ORDER BY [t0].[Created]");

            // AssertHelper("WITH cet0([ROW_NUMBER],[Id],[Name],[Created]) AS (SELECT ROW_NUMBER() OVER ( ORDER BY [t0].[Created]) AS [ROW_NUMBER], [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Name] = @p0)SELECT [Id],[Name],[Created] FROM cet0 [tc0] WHERE [tc0].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2 ORDER BY [tc0].[ROW_NUMBER]");

        }
        [TestMethod]
        public void TestMethod51()
        {

            var q = Dc.GetTable<App>().Where(p => p.Name == "").OrderBy(p => p.Created).Select(p => p);
            var l = q.ToPagedList(2, 5);


            AssertHelper("SELECT COUNT(*) FROM [App] [t0] WHERE [t0].[Name] = @p0");
            AssertHelper("WITH cet0([ROW_NUMBER],[Id],[Name],[Created]) AS ( SELECT ROW_NUMBER() OVER ( ORDER BY [Created]) AS [ROW_NUMBER],[Id],[Name],[Created] FROM (SELECT [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Name] = @p0) AS [tmpT1])SELECT [Id],[Name],[Created] FROM cet0 [tc0] WHERE [tc0].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2 ORDER BY [tc0].[ROW_NUMBER]");

            // AssertHelper("WITH cet0([ROW_NUMBER],[Id],[Name],[Created]) AS (SELECT ROW_NUMBER() OVER ( ORDER BY [t0].[Created]) AS [ROW_NUMBER], [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Name] = @p0)SELECT [Id],[Name],[Created] FROM cet0 [tc0] WHERE [tc0].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2 ORDER BY [tc0].[ROW_NUMBER]");

        }


        [TestMethod]
        public void TestMethod52()
        {
            var q = Dc.GetTable<App>().Where(p => p.Name == "").OrderBy(p => p.Created).Select(p => p);
            var l = q.ToPagedList(1, 1);

            q = Dc.GetTable<App>().Where(p => p.Name == "").OrderBy(p => p.Created).Select(p => p);
            q.Skip(0).Take(1).ToList();
            AssertHelper("SELECT COUNT(*) FROM [App] [t0] WHERE [t0].[Name] = @p0");
            AssertHelper("SELECT TOP (@p0) [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Name] = @p1 ORDER BY [t0].[Created]");

        }
        [TestMethod]
        public void TestMethod521()
        {
            var q = Dc.GetTable<App>().Where(p => p.Name == "").OrderBy(p => p.Created).Select(p => p);
            q.Skip(0).Take(1).ToList();
            AssertHelper("SELECT TOP (@p0) [t0].[Id] AS [Id],[t0].[Name] AS [Name],[t0].[Created] AS [Created] FROM [App] [t0] WHERE [t0].[Name] = @p1 ORDER BY [t0].[Created]");
        }
        [TestMethod]
        public void TestMethod53()
        {
            var q = from a in Dc.GetTable<App>()
                    orderby a.Id descending
                    select new { a.Id };
            q.Distinct().ToPagedList(2, 1);
            AssertHelper("SELECT COUNT(*) FROM [App] [t0]");

            AssertHelper("WITH cet0([ROW_NUMBER],[Id]) AS ( SELECT ROW_NUMBER() OVER ( ORDER BY [Id] DESC) AS [ROW_NUMBER],[Id] FROM (SELECT DISTINCT [t0].[Id] AS [Id] FROM [App] [t0]) AS [tmpT1])SELECT [Id] FROM cet0 [tc0] WHERE [tc0].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1 ORDER BY [tc0].[ROW_NUMBER]");
        }
        [TestMethod]
        public void TestMethod54()
        {

            var q = from a in Dc.GetTable<App>()
                    join u in Dc.GetTable<AppUser>() on a.Id equals u.AppId
                    orderby a.Name descending
                    where u.Name.Contains("1")
                    select new { a.Id, Name = u.Name };
            q.Distinct().ToPagedList(2, 1);
            AssertHelper("SELECT COUNT(*) FROM [App] [t0] INNER JOIN [AppUser] [t1] ON [t0].[Id]=[t1].[AppId] WHERE [t1].[Name] LIKE @p0");
            AssertHelper("WITH cet0([ROW_NUMBER],[Id],[Name]) AS ( SELECT ROW_NUMBER() OVER ( ORDER BY [Name] DESC) AS [ROW_NUMBER],[Id],[Name] FROM (SELECT DISTINCT [t0].[Id] AS [Id],[t1].[Name] AS [Name] FROM [App] [t0] INNER JOIN [AppUser] [t1] ON [t0].[Id]=[t1].[AppId] WHERE [t1].[Name] LIKE @p0) AS [tmpT1])SELECT [Id],[Name] FROM cet0 [tc0] WHERE [tc0].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2 ORDER BY [tc0].[ROW_NUMBER]");
        }
        [TestMethod]
        public void TestMethod55()
        {

            var q = from a in Dc.GetTable<App>()
                    join u in Dc.GetTable<AppUser>() on a.Id equals u.AppId
                    orderby a.Name descending
                    where u.Name.Contains("1")
                    select new { a.Id, Name = u.Name };
            var dfd = q.Distinct().Count();
            AssertHelper("SELECT COUNT(*) FROM [App] [t0] INNER JOIN [AppUser] [t1] ON [t0].[Id]=[t1].[AppId] WHERE [t1].[Name] LIKE @p0");
            AssertHelper("WITH cet0([ROW_NUMBER],[Id],[Name]) AS ( SELECT ROW_NUMBER() OVER ( ORDER BY [Name] DESC) AS [ROW_NUMBER],[Id],[Name] FROM (SELECT DISTINCT [t0].[Id] AS [Id],[t1].[Name] AS [Name] FROM [App] [t0] INNER JOIN [AppUser] [t1] ON [t0].[Id]=[t1].[AppId] WHERE [t1].[Name] LIKE @p0) AS [tmpT1])SELECT [Id],[Name] FROM cet0 [tc0] WHERE [tc0].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2 ORDER BY [tc0].[ROW_NUMBER]");
        }
        [TestMethod]
        public void TestMethod6()
        {
            var q = from a in Dc.GetTable<App>()
                    join u in Dc.GetTable<AppUser>() on a.Id equals u.AppId
                    select new { a.Id, u.Name };
            q.ToArray();

            AssertHelper("SELECT [t0].[Id] AS [Id],[t1].[Name] AS [Name] FROM [App] [t0] INNER JOIN [AppUser] [t1] ON [t0].[Id]=[t1].[AppId]");
        }
        [TestMethod]
        public void TestMethod61()
        {
            var q = from a in Dc.GetTable<App>()
                    join u in Dc.GetTable<AppUser>() on a.Id equals u.AppId
                    join a1 in Dc.GetTable<App>() on u.AppId equals a1.Id
                    where a.Name == ""
                    orderby a1.Created
                    select new { a.Id, u.Name };

            q.ToArray();
            AssertHelper("SELECT [t0].[Id] AS [Id],[t1].[Name] AS [Name] FROM [App] [t0] INNER JOIN [AppUser] [t1] ON [t0].[Id]=[t1].[AppId]  INNER JOIN [App] [t2] ON [t1].[AppId]=[t2].[Id] WHERE [t0].[Name] = @p0 ORDER BY [t2].[Created]");
        }

        [TestMethod]
        public void TestMethod7()
        {
            var q = from a in Dc.GetTable<App>()
                    orderby a.Id descending
                    select new { a.Id };
            q.Distinct().Count();
            AssertHelper("SELECT COUNT(*) FROM [App] [t0]");

            AssertHelper("WITH cet0([ROW_NUMBER],[Id]) AS ( SELECT ROW_NUMBER() OVER ( ORDER BY [Id] DESC) AS [ROW_NUMBER],[Id] FROM (SELECT DISTINCT [t0].[Id] AS [Id] FROM [App] [t0]) AS [tmpT1])SELECT [Id] FROM cet0 [tc0] WHERE [tc0].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1 ORDER BY [tc0].[ROW_NUMBER]");
        }
        [TestMethod]
        public void TestMethodD()
        {
            Dc.Delete<App>(a => a.Name == "xyz");//.Where(p.Name == "")
            //  Dc.SubmitChanges();
            AssertHelper("DELETE [t0] FROM [App] AS [t0] WHERE [t0].[Name] = @p0");
        }
        [TestMethod]
        public void TestMethodD1()
        {
            Dc.Delete<App>(q => q.Name == "xyz");//.Where(p.Name == "")
            // Dc.SubmitChanges();
            AssertHelper("DELETE [t0] FROM [App] AS [t0] WHERE [t0].[Name] = @p0");
        }
        [TestMethod]
        public void TestMethodD2()
        {
            Dc.Delete<App>(Dc.GetTable<App>().Where(a => a.Name == "xyz"));//.Where(p.Name == "")
             Dc.SubmitChanges();
            AssertHelper("DELETE [t0] FROM [App] AS [t0] WHERE [t0].[Name] = @p0");
        }
        [TestMethod]
        public void TestMethodD3()
        {
            Dc.Delete<App>(Dc.GetTable<App>().Join(Dc.GetTable<AppUser>(), a => a.Id, u => u.AppId,
                (a, u) => new { a, u }).Where(p => p.a.Name == "xyz").Select(p => p.a));
            //   Dc.SubmitChanges();
            AssertHelper("DELETE [t0] FROM [App] AS [t0] INNER JOIN [AppUser] [t1] ON [t0].[Id]=[t1].[AppId] WHERE [t0].[Name] = @p0");
        }

        [TestMethod]
        public void TestMethodU()
        {
            Dc.Update<App>(p => new App { Name = "newname" }, p => p.Name == "xyz");
            //   Dc.SubmitChanges();
            AssertHelper("UPDATE [t0] SET [t0].[Name] = @p0 FROM [App] AS [t0] WHERE [t0].[Name] = @p1");
        }
        [TestMethod]
        public void TestMethodU5()
        {
            Dc.Update<App>(new App { Name = "newname", Id = String.Empty });
            // Dc.SubmitChanges();
            AssertHelper("UPDATE [t0] SET [t0].[Created] = @p0,[t0].[Name] = @p1 FROM [App] AS [t0] WHERE [t0].[Id] = @p2");
        }
        [TestMethod]
        public void TestMethodI5()
        {
            Dc.Insert<App>(Dc.GetTable<AppUser>().Where(p => p.Name == "xyz").Select(p => new App() { Id = p.Id, Name = "app" + p.Name }));

            //  Dc.SubmitChanges();

            AssertHelper("INSERT INTO [App] ([Id],[Name]) SELECT [t0].[Id] AS [Id],@p0 + [t0].[Name] AS [Name] FROM [AppUser] [t0] WHERE [t0].[Name] = @p1");
            // Assert.IsTrue(log.Contains(";Text;String:app,String:xyz;INSERT INTO [App] ([Id],[Name])SELECT [t0].[Id] AS [Id],@p0 + [t0].[Name] AS [Name] FROM [AppUser] [t0] WHERE [t0].[Name] = @p1\r\n"));
        }
    }
}
